1 Public Class FrmDEFFECTIVE_STOCKS_ADD
2 Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
3 Pending_ID = 0
4 Pending_Item_ID = 0
5 Me.Close()
6 End Sub
7
8 Private Sub FrmDEFFECTIVE_STOCKS_ADD_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
9 Pending_ID = 0
10 Pending_Item_ID = 0
11 Me.Text = "Defective Stock Details"
12 End Sub
13
14 Private Sub FrmDEFFECTIVE_STOCKS_ADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
15 Dim i As Integer
16 lstitems.Items.Clear()
17 If Split(Me.Text, " - ")(1) = "Add" Then
18 cmdAdd.Enabled = True
19 cmdSave.Enabled = True
20 txtpo.Text = ""
21 txtdeliver.Text = ""
22 txtadd.Text = ""
23 lstitems.Items.Clear()
24 Label7.Text = "Purchase ID"
25 FrmDEFFECTIVE_PO_LIST.ShowDialog()
26 Else
27 cmdAdd.Enabled = False
28 Label7.Text = "Defective ID"
29 txtpo.Text = globalID
30 sqlSTR = "SELECT *, *, *, *, * " & _
31 "FROM (((((TBL_Suppliers " & _
32 "INNER JOIN TBL_Purchase_Order ON TBL_Suppliers.Supp_ID = TBL_Purchase_Order.Supp_ID) " & _
33 "INNER JOIN TBL_Deffective_PO ON TBL_Purchase_Order.Purchase_ID = TBL_Deffective_PO.Purchase_ID) " & _
34 "INNER JOIN TBL_Deffective_PO_Details ON TBL_Deffective_PO.Purchase_ID = TBL_Deffective_PO_Details.Purchase_ID AND " & _
35 "TBL_Deffective_PO.DEF_PO_ID = TBL_Deffective_PO_Details.DEF_PO_ID) " & _
36 "INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID " & _
37 "AND TBL_Deffective_PO_Details.Item_ID = TBL_Purchase_Detail.Item_ID) " & _
38 "INNER JOIN TBL_Category_Item_File ON TBL_Deffective_PO_Details.Item_ID = TBL_Category_Item_File.Item_ID) " & _
39 "WHERE TBL_Deffective_PO.DEF_PO_ID =" & txtpo.Text
40 'MsgBox(txtpo.Text)
41 'MsgBox(sqlSTR)
42 ExecuteSQLQuery(sqlSTR)
43 If sqlDT.Rows.Count > 0 Then
44 txtSuppname.Text = R_Change(sqlDT.Rows(0)("suppname"))
45 txtadd.Text = R_Change(sqlDT.Rows(0)("address"))
46 txtdeliver.Text = R_Change(sqlDT.Rows(0)("delivery_term"))
47 dtRETURN.Value = sqlDT.Rows(0)("Return_Date")
48 'MsgBox(Pending_QTY)
49 For i = 0 To sqlDT.Rows.Count - 1
50 With lstitems
51 .Items.Add(sqlDT.Rows(i)("Item_ID"))
52 .Items((.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("Purchase_Detail_ID"))
53 .Items((.Items.Count - 1)).SubItems.Add(R_Change(sqlDT.Rows(i)("Item_Name")))
54 .Items((.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("Item_description"))
55 .Items((.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("Item_Price"))
56 .Items((.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("Item_QTY"))
57 .Items((.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("def_QTY"))
58 .Items((.Items.Count - 1)).SubItems.Add(sqlDT.Rows(i)("Unit"))
59 End With
60 Next
61 End If
62 'CHECK IF ITS FULLY RETURNED
63 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return WHERE Def_PO_ID =" & txtpo.Text & _
64 " AND Fully_Return='Yes'" & _
65 " ORDER BY Def_PO_ID ASC"
66 ExecuteSQLQuery(sqlSTR)
67 If sqlDT.Rows.Count > 0 Then
68 MsgBox("Can't modify the data because its already been returned !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
69 cmdAdd.Enabled = False
70 cmdEdit.Enabled = False
71 cmdSave.Enabled = False
72 Exit Sub
73 Else
74 cmdAdd.Enabled = True
75 cmdEdit.Enabled = True
76 End If
77 End If
78 If Pending_ID > 0 Then
79 cmdEdit.Enabled = False
80 cmdAdd.Enabled = False
81 Else
82 cmdAdd.Enabled = True
83 cmdEdit.Enabled = True
84 End If
85 End Sub
86
87 Private Sub txtpo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtpo.Click
88 If Split(Me.Text, " - ")(1) = "Edit" Then Exit Sub
89 FrmDEFFECTIVE_PO_LIST.ShowDialog()
90 End Sub
91
92 Private Sub txtpo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtpo.TextChanged
93
94 If Split(Me.Text, " - ")(1) = "Edit" Then Exit Sub
95 If txtpo.Text = "" Then Exit Sub
96 sqlSTR = "SELECT * FROM TBL_Deffective_PO WHERE Purchase_ID =" & txtpo.Text
97 ExecuteSQLQuery(sqlSTR)
98 'If sqlDT.Rows.Count > 0 And Pending_ID = 0 Then
99 ' MsgBox("Purchase Order ' " & txtpo.Text & " is already on the list Date " & sqlDT.Rows(0)("Return_Date"))
100 ' Me.Close()
101 ' Exit Sub
102 ' Else
103 ' sqlSTR = "SELECT *, *, *, *, * " & _
104 ' "FROM (((((TBL_Suppliers " & _
105 ' "INNER JOIN TBL_Purchase_Order ON TBL_Suppliers.Supp_ID = TBL_Purchase_Order.Supp_ID) " & _
106 ' "INNER JOIN TBL_Deffective_PO ON TBL_Purchase_Order.Purchase_ID = TBL_Deffective_PO.Purchase_ID) " & _
107 ' "INNER JOIN TBL_Deffective_PO_Details ON TBL_Deffective_PO.Purchase_ID = TBL_Deffective_PO_Details.Purchase_ID) " & _
108 ' "INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID) " & _
109 ' "INNER JOIN TBL_Category_Item_File ON TBL_Deffective_PO_Details.Item_ID = TBL_Category_Item_File.Item_ID) " & _
110 ' "WHERE TBL_Purchase_Order.Purchase_ID =" & txtpo.Text
111
112 ' sqlSTR = "SELECT *, *, * " & _
113 ' "FROM ((TBL_Deffective_PO_Details " & _
114 ' "INNER JOIN TBL_Category_Item_File ON TBL_Deffective_PO_Details.Item_ID = TBL_Category_Item_File.Item_ID) " & _
115 ' "INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_ID = TBL_Purchase_Detail.Purchase_ID " & _
116 ' "AND TBL_Deffective_PO_Details.Item_ID = TBL_Purchase_Detail.Item_ID) " & _
117 ' "WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text
118
119 '" AND TBL_Deffective_PO_Details.Item_ID =" & Pending_Item_ID
120 ' If Pending_ID > 0 Then
121 ' sqlSTR = sqlSTR & " AND TBL_Purchase_Detail.Item_ID =" & Pending_Item_ID
122 ' Else
123 ' sqlSTR = sqlSTR & ""
124 'End If
125
126 sqlSTR = "SELECT *, * " & _
127 "FROM TBL_Purchase_Detail " & _
128 "INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID " & _
129 "WHERE TBL_Purchase_Detail.Purchase_ID =" & txtpo.Text & _
130 " AND TBL_Purchase_Detail.Item_ID =" & Pending_Item_ID
131 ExecuteSQLQuery(sqlSTR)
132 If sqlDT.Rows.Count > 0 Then
133 For i = 0 To sqlDT.Rows.Count - 1
134 lstitems.Items.Add(sqlDT.Rows(i)("Item_ID"))
135 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Purchase_Detail_ID"))
136 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(R_Change(sqlDT.Rows(i)("Item_Name")))
137 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Item_Description"))
138 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Item_Price"))
139 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Item_QTY"))
140 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(Pending_QTY)
141 'lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("def_QTY") + Pending_QTY)
142 lstitems.Items(lstitems.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Unit_Measure"))
143 Next
144 Me.Text = "Defective Stock Details - Add"
145 Else
146 Me.Text = "Defective Stock Details - Add"
147 End If
148 'Me.Text = ""
149
150 'End If
151 sqlSTR = "SELECT *, *, *, * " & _
152 "FROM (((TBL_Suppliers " & _
153 "INNER JOIN TBL_Purchase_Order ON TBL_Suppliers.Supp_ID = TBL_Purchase_Order.Supp_ID) " & _
154 "INNER JOIN TBL_Purchase_Detail ON TBL_Purchase_Order.Purchase_ID = TBL_Purchase_Detail.Purchase_ID) " & _
155 "INNER JOIN TBL_Category_Item_File ON TBL_Purchase_Detail.Item_ID = TBL_Category_Item_File.Item_ID) " & _
156 "WHERE TBL_Purchase_Order.Purchase_ID =" & txtpo.Text
157 'MsgBox(sqlSTR)
158 ExecuteSQLQuery(sqlSTR)
159 txtSuppname.Text = R_Change(sqlDT.Rows(0)("suppname"))
160 txtadd.Text = R_Change(sqlDT.Rows(0)("address"))
161 txtdeliver.Text = R_Change(sqlDT.Rows(0)("delivery_term"))
162 End Sub
163
164 Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
165 'FrmDEFFECTIVE_STOCKS_DATA_ADD.ShowDialog()
166 If txtpo.Text <> "" Then
167 FormShow(FrmDEFFECTIVE_STOCKS_DATA_ADD, False, 0, 0)
168 End If
169 End Sub
170
171 Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
172 Dim def_po_id As Integer
173 On Error Resume Next
174 Dim I As Integer
175 Dim total_price As Double
176 Dim ix As Integer, item_qty As Integer
177 If Split(Me.Text, " - ")(1) = "Add" Then
178 'MsgBox("A")
179 If lstitems.Items.Count = 0 Then
180 MsgBox("No item details, please add details !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
181 Exit Sub
182 End If
183 '----
184 'sqlSTR = "SELECT * FROM TBL_Deffective_PO WHERE Purchase_ID =" & txtpo.Text
185 'ExecuteSQLQuery(sqlSTR)
186 If Pending_ID > 0 Then
187 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Details WHERE Pending_ID =" & Pending_ID
188 ExecuteSQLQuery(sqlSTR)
189 If sqlDT.Rows.Count > 0 Then
190 MsgBox("Record has already been assign as defective !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
191 Exit Sub
192 End If
193 End If
194
195 '----
196 'If sqlDT.Rows.Count = 0 Then
197 sqlSTR = "INSERT INTO TBL_Deffective_PO (Purchase_ID, SupplierName, Delivery_term, Address, Return_Date, Pending_ID) " & _
198 "VALUES (" & txtpo.Text & ", " _
199 & "'" & R_eplace(txtSuppname.Text) & "', " _
200 & "'" & R_eplace(txtdeliver.Text) & "', " _
201 & "'" & R_eplace(txtadd.Text) & "', " _
202 & "'" & Format(dtRETURN.Value, "MM/dd/yyyy") & "', " _
203 & Pending_ID & ")"
204 ExecuteSQLQuery(sqlSTR)
205 'End If
206 sqlSTR = "SELECT * FROM TBL_Deffective_PO ORDER BY DEF_PO_ID DESC"
207 ExecuteSQLQuery(sqlSTR)
208 def_po_id = sqlDT.Rows(0)("DEF_PO_ID")
209
210 For I = 0 To lstitems.Items.Count - 1
211 sqlSTR = "INSERT INTO TBL_Deffective_PO_Details (Pending_ID, Purchase_ID, DEF_PO_ID, Purchase_Detail_ID, Item_ID, Def_Qty, Unit) " & _
212 "VALUES (" & IIf(Pending_Item_ID = lstitems.Items(I).Text, Pending_ID, 0) & ", " _
213 & txtpo.Text & ", " _
214 & def_po_id & ", " _
215 & lstitems.Items(I).SubItems(1).Text & ", " _
216 & lstitems.Items(I).Text & ", " _
217 & lstitems.Items(I).SubItems(6).Text & ", " _
218 & "'" & lstitems.Items(I).SubItems(7).Text & "')"
219 ExecuteSQLQuery(sqlSTR)
220 'sqlSTR = "UPDATE TBL_Purchase_Detail SET Item_QTY =" & "Item_QTY - " & lstitems.Items(I).SubItems(6).Text & ", " _
221 ' & "Total_Price = " & CDbl((" Item_QTY - " & lstitems.Items(I).SubItems(6).Text)) * (2) _
222 ' & " WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
223 'MsgBox(sqlSTR)
224
225 'update purchase detail
226 'total_price = CDbl(lstitems.Items(I).SubItems(5).Text - lstitems.Items(I).SubItems(6).Text) * lstitems.Items(I).SubItems(4).Text
227 'sqlSTR = "UPDATE TBL_Purchase_Detail SET Item_QTY =" & "Item_QTY - " & lstitems.Items(I).SubItems(6).Text & ", " _
228 ' & "Total_Price = " & total_price _
229 ' & " WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
230 'ExecuteSQLQuery(sqlSTR)
231
232 'update stocks
233 sqlSTR = "UPDATE TBL_Stocks_Balances SET Item_QTY =" & "Item_QTY - " & lstitems.Items(I).SubItems(6).Text & _
234 " WHERE Item_ID =" & lstitems.Items(I).Text
235 ExecuteSQLQuery(sqlSTR)
236 Next
237 Audit_Trail(xUser_ID, TimeOfDay, "Add New Deffective Stocks")
238 Else
239 For I = 0 To lstitems.Items.Count - 1
240 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Details WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
241 ExecuteSQLQuery(sqlSTR)
242 If sqlDT.Rows.Count > 0 Then
243 ix = sqlDT.Rows(0)("def_QTY")
244 Else
245 ix = 0
246 End If
247
248 If sqlDT.Rows.Count > 0 Then
249 'IF RECORD EXISTS THEN UPDATE
250 'MsgBox("A")
251
252 If sqlDT.Rows(0)("Def_QTY") > lstitems.Items(I).SubItems(6).Text Then
253 ' MsgBox("add")
254 'add
255 ' sqlSTR = "UPDATE TBL_Deffective_PO_Details, TBL_Purchase_Detail, TBL_Stocks_Balances " & _
256 ' "SET def_QTY =" & sqlDT.Rows(0)("Def_QTY") - (sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text) & ", " _
257 ' & "TBL_Purchase_Detail.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & ", " _
258 ' & "TBL_Stocks_Balances.Item_QTY =" & "TBL_Stocks_Balances.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & _
259 ' " WHERE TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID " & _
260 ' " AND TBL_Stocks_Balances.Item_ID = TBL_Purchase_Detail.Item_ID " & _
261 ' " AND TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
262 ' " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
263
264 ' total_price = (lstitems.Items(I).SubItems(5).Text + (sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text)) * lstitems.Items(I).SubItems(4).Text
265
266 'sqlSTR = "UPDATE ((TBL_Deffective_PO_Details INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID) " & _
267 ' "INNER JOIN TBL_Stocks_Balances ON TBL_Purchase_Detail.Item_ID = TBL_Stocks_Balances.Item_ID) " & _
268 ' "SET def_QTY =" & sqlDT.Rows(0)("Def_QTY") - (sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text) & ", " _
269 ' & "Return_Date ='" & Format(dtRETURN.Value, "MM/dd/yyyy") & "', " _
270 ' & "TBL_Purchase_Detail.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & ", " _
271 ' & "TBL_Purchase_Detail.Total_Price=" & total_price & ", " _
272 ' & "TBL_Stocks_Balances.Item_QTY =" & "TBL_Stocks_Balances.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & _
273 ' " WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
274 ' " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
275 'ExecuteSQLQuery(sqlSTR)
276
277
278 sqlSTR = "UPDATE TBL_Deffective_PO_Details " & _
279 "SET def_QTY =" & ix - (ix - lstitems.Items(I).SubItems(6).Text) & _
280 " WHERE TBL_Deffective_PO_Details.DEF_PO_ID =" & txtpo.Text & _
281 " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
282 ExecuteSQLQuery(sqlSTR)
283
284 sqlSTR = "SELECT * FROM TBL_Purchase_Detail WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
285 ExecuteSQLQuery(sqlSTR)
286 item_qty = sqlDT.Rows(0)("item_qty")
287
288 'UPDATE PURCHASE DETAILS
289 ' sqlSTR = "UPDATE TBL_Purchase_Detail " & _
290 ' "SET Item_QTY =" & "Item_QTY + " & ix - lstitems.Items(I).SubItems(6).Text & ", " _
291 ' & "TBL_Purchase_Detail.Total_Price=" & total_price & _
292 ' " WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
293 ' ExecuteSQLQuery(sqlSTR)
294
295 sqlSTR = "UPDATE TBL_Stocks_Balances " & _
296 "SET Item_QTY =" & item_qty + (ix - lstitems.Items(I).SubItems(6).Text) & _
297 " WHERE Item_ID =" & lstitems.FocusedItem.Text
298 ExecuteSQLQuery(sqlSTR)
299 ElseIf sqlDT.Rows(0)("Def_QTY") < lstitems.Items(I).SubItems(6).Text Then
300 ' MsgBox("Deduct")
301 'deduct
302 ' sqlSTR = "UPDATE TBL_Deffective_PO_Details, TBL_Purchase_Detail, TBL_Stocks_Balances " & _
303 ' "SET def_QTY =" & sqlDT.Rows(0)("Def_QTY") + (lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY")) & ", " _
304 ' & "TBL_Purchase_Detail.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY - " & lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY") & ", " _
305 ' & "TBL_Stocks_Balances.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY - " & lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY") & _
306 ' " WHERE TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID " & _
307 ' " AND TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
308 ' " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
309
310 '---------------
311 ' sqlSTR = "UPDATE TBL_Deffective_PO_Details " & _
312 ' "INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID " & _
313 ' "INNER JOIN TBL_Stocks_Balances ON TBL_Purchase_Detail.Item_ID = TBL_Stocks_Balances.Item_ID " & _
314 ' "SET def_QTY =" & sqlDT.Rows(0)("Def_QTY") + (lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY")) & ", " _
315 ' & "TBL_Purchase_Detail.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY - " & lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY") & ", " _
316 ' & "TBL_Purchase_Detail.Total_Price=" & total_price & ", " _
317 ' & "TBL_Stocks_Balances.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY - " & lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY") & _
318 ' " WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
319 ' " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
320
321 '----------------
322
323 total_price = (lstitems.Items(I).SubItems(5).Text - (lstitems.Items(I).SubItems(6).Text - sqlDT.Rows(0)("Def_QTY"))) * lstitems.Items(I).SubItems(4).Text
324
325 ' sqlSTR = "UPDATE TBL_Deffective_PO " & _
326 ' "SET Return_Date ='" & Format(dtRETURN.Value, "MM/dd/yyyy") & "' WHERE Purchase_ID =" & txtpo.Text
327 ' ExecuteSQLQuery(sqlSTR)
328
329 sqlSTR = "UPDATE TBL_Deffective_PO_Details " & _
330 "SET def_QTY =" & ix + (lstitems.Items(I).SubItems(6).Text - ix) & _
331 " WHERE TBL_Deffective_PO_Details.DEF_PO_ID =" & txtpo.Text & _
332 " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
333 ExecuteSQLQuery(sqlSTR)
334
335 sqlSTR = "SELECT * FROM TBL_Purchase_Detail WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
336 ExecuteSQLQuery(sqlSTR)
337 item_qty = sqlDT.Rows(0)("item_qty")
338
339 'UPDATE PURCHASE DETAIL
340 'sqlSTR = "UPDATE TBL_Purchase_Detail " & _
341 ' "SET Item_QTY =" & "Item_QTY - " & lstitems.Items(I).SubItems(6).Text - ix & ", " _
342 ' & "TBL_Purchase_Detail.Total_Price=" & total_price & _
343 ' " WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
344 'ExecuteSQLQuery(sqlSTR)
345
346
347 'MsgBox(sqlDT.Rows(0)("Item_QTY"))
348 sqlSTR = "UPDATE TBL_Stocks_Balances " & _
349 "SET Item_QTY = Item_QTY -" & CDbl(lstitems.Items(I).SubItems(6).Text) & _
350 " WHERE Item_ID =" & lstitems.Items(I).Text 'FocusedItem.Text
351 '"SET Item_QTY =" & item_qty - (lstitems.Items(I).SubItems(6).Text - ix) & _
352
353 'MsgBox(sqlSTR)
354 ExecuteSQLQuery(sqlSTR)
355 End If
356 Else
357 'If RECORD NOT EXISTS THEN ADD
358 'MsgBox("extra")
359 'MsgBox("Extra")
360 sqlSTR = "SELECT * FROM TBL_Deffective_PO WHERE Purchase_ID =" & txtpo.Text
361 ExecuteSQLQuery(sqlSTR)
362 If sqlDT.Rows.Count = 0 Then
363 sqlSTR = "INSERT INTO TBL_Deffective_PO (Purchase_ID, SupplierName, Delivery_term, Address, Return_Date) " & _
364 "VALUES (" & txtpo.Text & ", " _
365 & "'" & R_eplace(txtSuppname.Text) & "', " _
366 & "'" & R_eplace(txtdeliver.Text) & "', " _
367 & "'" & R_eplace(txtadd.Text) & "', " _
368 & "'" & Format(dtRETURN.Value, "MM/dd/yyyy") & "')"
369 ExecuteSQLQuery(sqlSTR)
370 End If
371
372 sqlSTR = "INSERT INTO TBL_Deffective_PO_Details (Purchase_ID, Purchase_Detail_ID, Item_ID, Def_Qty, Unit) " & _
373 "VALUES (" & txtpo.Text & ", " _
374 & lstitems.Items(I).SubItems(1).Text & ", " _
375 & lstitems.Items(I).Text & ", " _
376 & lstitems.Items(I).SubItems(6).Text & ", " _
377 & "'" & lstitems.Items(I).SubItems(7).Text & "')"
378 ExecuteSQLQuery(sqlSTR)
379 '---
380 total_price = (lstitems.Items(I).SubItems(5).Text - lstitems.Items(I).SubItems(6).Text) * lstitems.Items(I).SubItems(4).Text
381
382 ' sqlSTR = "UPDATE ((TBL_Deffective_PO_Details INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID) " & _
383 ' "INNER JOIN TBL_Stocks_Balances ON TBL_Purchase_Detail.Item_ID = TBL_Stocks_Balances.Item_ID) " & _
384 ' "SET def_QTY =" & lstitems.Items(I).SubItems(6).Text & ", " _
385 ' & "TBL_Purchase_Detail.Item_QTY =" & lstitems.Items(I).SubItems(5).Text - lstitems.Items(I).SubItems(6).Text & ", " _
386 ' & "TBL_Purchase_Detail.Total_Price=" & total_price & ", " _
387 ' & "TBL_Stocks_Balances.Item_QTY =" & lstitems.Items(I).SubItems(5).Text - lstitems.Items(I).SubItems(6).Text & _
388 ' " WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
389 ' " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
390 sqlSTR = "UPDATE TBL_Deffective_PO " & _
391 "SET Return_Date ='" & Format(dtRETURN.Value, "MM/dd/yyyy") & "' WHERE Purchase_ID =" & txtpo.Text
392 ExecuteSQLQuery(sqlSTR)
393
394 sqlSTR = "UPDATE TBL_Deffective_PO_Details " & _
395 "SET def_QTY =" & (lstitems.Items(I).SubItems(6).Text) & _
396 " WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
397 " AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
398 ExecuteSQLQuery(sqlSTR)
399
400 'UPDATE PURCHASE DETAILS
401 ' sqlSTR = "UPDATE TBL_Purchase_Detail " & _
402 ' "SET Item_QTY =" & "Item_QTY - " & lstitems.Items(I).SubItems(6).Text & ", " _
403 ' & "TBL_Purchase_Detail.Total_Price=" & total_price & _
404 ' " WHERE Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text
405 ' ExecuteSQLQuery(sqlSTR)
406
407 'MsgBox(sqlDT.Rows(0)("Item_QTY"))
408 sqlSTR = "UPDATE TBL_Stocks_Balances " & _
409 "SET Item_QTY = Item_QTY - " & lstitems.Items(I).SubItems(6).Text & _
410 " WHERE Item_ID =" & lstitems.Items(I).Text ' FocusedItem.Text
411 'MsgBox(sqlSTR)
412 ExecuteSQLQuery(sqlSTR)
413 End If
414 Next
415 sqlSTR = "UPDATE TBL_Deffective_PO " & _
416 "SET Return_Date ='" & Format(dtRETURN.Value, "MM/dd/yyyy") & "' WHERE Purchase_ID =" & txtpo.Text
417 ExecuteSQLQuery(sqlSTR)
418 Audit_Trail(xUser_ID, TimeOfDay, "Edit Deffective Stocks")
419 End If
420 'If Pending_ID > 0 Then
421 ' sqlSTR = "UPDATE TBL_Pending_Item SET Returnx = 'Yes' " & ", " _
422 ' & " Return_Date ='" & Format(dtRETURN.Value, "MM/dd/yyyy") & "'" & _
423 ' " WHERE Pending_ID =" & Pending_ID
424 '
425 ' ExecuteSQLQuery(sqlSTR)
426 ' End If
427 MsgBox("Record successfuly updated !!", MsgBoxStyle.Information, "Sales and Inventory")
428 With FrmDEFFECTIVE_RETURN_STOCKS
429 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Delivery_Term as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',','), Pending_ID AS 'Pending ID' FROM TBL_Deffective_PO " & _
430 "WHERE Return_Date ='" & Format(.dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY DEF_PO_ID ASC"
431 FillListView(ExecuteSQLQuery(sqlSTR), .lstdeffect, 0)
432 For I = 0 To .lstdeffect.Items.Count - 1
433 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return WHERE Def_PO_ID =" & .lstdeffect.Items(I).Text & _
434 " AND Fully_Return='Yes'" & _
435 " ORDER BY Def_PO_ID ASC"
436 ExecuteSQLQuery(sqlSTR)
437 If sqlDT.Rows.Count > 0 Then
438 .lstdeffect.Items(I).ForeColor = Color.Brown
439 Else
440 .lstdeffect.Items(I).ForeColor = Color.Black
441 End If
442 Next
443 End With
444
445 Pending_ID = 0
446 Me.Close()
447 End Sub
448
449 Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
450 If lstitems.Items.Count = 0 Then Exit Sub
451 lstitems.Focus()
452 With FrmDEFFECTIVE_STOCKS_DATA_ADD
453 .txtid.Text = Me.lstitems.FocusedItem.Text
454 .txtdtl.Text = Me.lstitems.FocusedItem.SubItems(1).Text
455 .txtname.Text = Me.lstitems.FocusedItem.SubItems(2).Text
456 .txtdesc.Text = Me.lstitems.FocusedItem.SubItems(3).Text
457 .txtprice.Text = Me.lstitems.FocusedItem.SubItems(4).Text
458 '.txtbarcode.Text = Me.lstitems.FocusedItem.SubItems(5).Text
459 .txtqty.Text = Me.lstitems.FocusedItem.SubItems(5).Text
460 .txtdefqty.Text = Me.lstitems.FocusedItem.SubItems(6).Text
461 .txtunit.Text = Me.lstitems.FocusedItem.SubItems(7).Text
462 End With
463 FormShow(FrmDEFFECTIVE_STOCKS_DATA_ADD, True, lstitems.FocusedItem.SubItems(1).Text, txtpo.Text)
464 End Sub
465 End Class
